﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.Odbc;

namespace URLWidgetDisplay
{
    public class AppointmentRepository
    {
        public static IEnumerable<Appointment> QueryAppointmentTable(string ClientId)
        {
            var AppointmentList = new List<Appointment>();
            var connectionString = ConfigurationManager.ConnectionStrings["AvatarDBPM"].ConnectionString;
            #region commandText
            var commandText = "SELECT appt_data.appointment_date as \"AppointmentDate\"," +
                                    "appt_data.appointment_start_time as \"StartTime\"," +
                                    "appt_data.appointment_end_time as \"EndTime\"," +
                                    "appt_data.ID as \"AppointmentId\"," +
                                    "appt_data.PATID as \"ClientId\"," +
                                    "appt_data.patient_name as \"ClientName\"," +
                                    "appt_data.STAFFID as \"StaffId\"," +
                                    "appt_data.staff_name as \"StaffName\"," +
                                    "appt_data.program_code as \"ProgramCode\"," +
                                    "appt_data.program_value as \"ProgramValue\"," +
                                    "appt_data.status_code as \"StatusCode\"," +
                                    "appt_data.status_value as \"StatusValue\"," +
                                    "appt_data.SERVICE_CODE as \"ScheduledServiceCode\"," +
                                    "appt_data.service_description as \"ScheduledServiceValue\"," +
                                    "appt_data.missed_visit_service_code as \"MissedVisitServiceCode\"," +
                                    "appt_data.missed_visit_service_desc as \"MissedVisitServiceValue\"," +
                                    "appt_data.posted_code as \"PostedCode\"," +
                                    "appt_data.posted_value as \"PostedValue\"," +
                                    "CASE WHEN appt_data.missed_visit_service_code IS NULL "+
                                    "THEN STRING('(',appt_data.SERVICE_CODE,') ',appt_data.service_description) "+
                                    "ELSE STRING('(',appt_data.missed_visit_service_code,') ',appt_data.missed_visit_service_desc) "+
                                    "END as \"CrosstabCount\","+
                                    "appt_data.appointment_notes as \"Notes\"" +
                                    "FROM SYSTEM.appt_data ";
            #endregion
            try
            {
                using (var connection = new OdbcConnection(connectionString))
                {
                    connection.Open();
                    using (var dbcommand = new OdbcCommand(commandText, connection))
                    {
                        if (!String.IsNullOrEmpty(ClientId))
                        {
                            commandText += "WHERE appt_data.PATID=?";
                            dbcommand.CommandText = commandText;
                            dbcommand.Parameters.Add(new OdbcParameter("PATID", ClientId));
                        }
                        using (var reader = dbcommand.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                #region ReadInValues
                                var appointment = new Appointment();
                                appointment.AppointmentDate = reader.GetDate(reader.GetOrdinal("AppointmentDate"));
                                appointment.AppointmentId = reader["AppointmentId"].ToString();
                                appointment.ClientId = reader["ClientId"].ToString();
                                appointment.ClientName = reader["ClientName"].ToString();
                                appointment.EndTime = reader["EndTime"].ToString();
                                appointment.MissedVisitServiceCode = reader["MissedVisitServiceCode"].ToString();
                                appointment.MissedVisitServiceValue = reader["MissedVisitServiceValue"].ToString();
                                appointment.Notes = reader["Notes"].ToString();
                                appointment.PostedCode = reader["PostedCode"].ToString();
                                appointment.PostedValue = reader["PostedValue"].ToString();
                                appointment.ProgramCode = reader["ProgramCode"].ToString();
                                appointment.ProgramValue = reader["ProgramValue"].ToString();
                                appointment.ScheduledServiceCode = reader["ScheduledServiceCode"].ToString();
                                appointment.ScheduledServiceValue = reader["ScheduledServiceValue"].ToString();
                                appointment.StaffId = reader["StaffId"].ToString();
                                appointment.StaffName = reader["StaffName"].ToString();
                                appointment.StartTime = reader["StartTime"].ToString();
                                appointment.StatusCode = reader["StatusCode"].ToString();
                                appointment.StatusValue = reader["StatusValue"].ToString();
                                appointment.CrosstabCount = reader["CrosstabCount"].ToString();
                                AppointmentList.Add(appointment);
                                #endregion
                            }
                        }
                    }
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
            }
            return AppointmentList.AsEnumerable();
        }
        public static IEnumerable<Appointment> GetAll()
        {
            return QueryAppointmentTable(String.Empty);
        }
        public static IEnumerable<Appointment> GetAppointmentsByClientId(string ClientId)
        {
            return QueryAppointmentTable(ClientId);
        }
    }
}